// StandardService.java
package com.medical.standard.service;

import entity.Standard;
import entity.StandardDetail;
import util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class StandardService {

    /**
     * 一级检索：根据标准编号检索标准清单
     */
    public List<Standard> searchStandards(String standardCode) {
        List<Standard> standardList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT id, standard_code, standard_name, english_name, " +
                    "reference_type, publish_date, implementation_date " +
                    "FROM standard WHERE standard_code LIKE ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + standardCode + "%");

            rs = pstmt.executeQuery();

            while (rs.next()) {
                Standard standard = new Standard(
                        rs.getInt("id"),
                        rs.getString("standard_code"),
                        rs.getString("standard_name"),
                        rs.getString("english_name"),
                        rs.getString("reference_type"),
                        rs.getDate("publish_date"),
                        rs.getDate("implementation_date")
                );
                standardList.add(standard);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return standardList;
    }

    /**
     * 二级检索：根据标准ID和关键词检索具体内容
     */
    public List<StandardDetail> searchStandardDetails(int standardId, String keyword) {
        List<StandardDetail> detailList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();

            // 从term表检索
            String termSql = "SELECT project_num, term_name, definition FROM term " +
                    "WHERE standard_id = ? AND (term_name LIKE ? OR definition LIKE ?)";
            pstmt = conn.prepareStatement(termSql);
            pstmt.setInt(1, standardId);
            pstmt.setString(2, "%" + keyword + "%");
            pstmt.setString(3, "%" + keyword + "%");

            rs = pstmt.executeQuery();
            while (rs.next()) {
                StandardDetail detail = new StandardDetail(
                        rs.getString("project_num"),
                        rs.getString("term_name"),
                        rs.getString("definition"),
                        "term"
                );
                detailList.add(detail);
            }
            rs.close();
            pstmt.close();

            // 从inspection表检索
            String inspectionSql = "SELECT project_num, project_name, project_requirement FROM inspection " +
                    "WHERE standard_id = ? AND (project_name LIKE ? OR project_requirement LIKE ?)";
            pstmt = conn.prepareStatement(inspectionSql);
            pstmt.setInt(1, standardId);
            pstmt.setString(2, "%" + keyword + "%");
            pstmt.setString(3, "%" + keyword + "%");

            rs = pstmt.executeQuery();
            while (rs.next()) {
                StandardDetail detail = new StandardDetail(
                        rs.getString("project_num"),
                        rs.getString("project_name"),
                        rs.getString("project_requirement"),
                        "inspection"
                );
                detailList.add(detail);
            }
            rs.close();
            pstmt.close();

            // 从requirement表检索
            String requirementSql = "SELECT project_num, project_name, project_requirement FROM requirement " +
                    "WHERE standard_id = ? AND (project_name LIKE ? OR project_requirement LIKE ?)";
            pstmt = conn.prepareStatement(requirementSql);
            pstmt.setInt(1, standardId);
            pstmt.setString(2, "%" + keyword + "%");
            pstmt.setString(3, "%" + keyword + "%");

            rs = pstmt.executeQuery();
            while (rs.next()) {
                StandardDetail detail = new StandardDetail(
                        rs.getString("project_num"),
                        rs.getString("project_name"),
                        rs.getString("project_requirement"),
                        "requirement"
                );
                detailList.add(detail);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return detailList;
    }
}